| Oracle 查询表空间使用率超慢问题一则 | 您所在的位置：网站首页 › oracle 查询速度 › Oracle 查询表空间使用率超慢问题一则 |
Oracle 查询表空间使用率超慢问题一则
|
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 查询表空间使用率超慢问题一则 近期，在运维的数据库中有一套 11g 和 一套 19c 的环境，使用如下 SQL 查看表空间使用率时竟然需要 1~2 分钟才可以查看结果，两套数据库数据库也就百 GB 级别，为何会这么慢呢？ SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a, (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY 4; 下面我们来一起看看： 查看执行计划记得以前遇到过一次也是查询很慢的情况，根据执行计划收集完一次数据字典统计信息就好了，那么这次到底是不是同样的问题呢？ 15:12:51 SYS@testogg> explain plan for SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a, (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY 4; Explained. Elapsed: 00:00:00.68 15:13:43 SYS@testogg> select * from table(dbms_xplan.display());DBA_FREE_SPACE 视图慢DBA_FREE_SPACE 视图有 2454 行，看看这个到底有多少行。  不看不知道，一看吓一跳。 DBA_FREE_SPACE 只有 1391 行，count（*）花费了 1 分 41 秒。那么我们来看一下它的执行计划，看看都访问了哪些对象。   由上图看出，主要访问了这几个系统表 FET$、TS$、RECYCLEBIN$、X$KTFBUE、UET$ 以及 NEW_LOST_wRITE_EXTENTS$，每一个都是有可能引起慢的原因，我们来收集一下统计信息看看。收集统计信息收集系统统计信息： exec dbms_stats.GATHER_SYSTEM_STATS; 收集动态性能视图基表的统计信息: exec dbms_stats.GATHER_FIXED_OBJECTS_STATS; 收集数据字典的统计信息: exec dbms_stats.GATHER_DICTIONARY_STATS; 收集用户的统计信息: exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SYS’) 收集表统计信息: exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SYS’,taname=>‘TS$’,CASCADE=>true) 如下图，收集统计信息没有任何改善，查询时间基本没变。  定位问题在 Oracle 的 MOS 中，有一篇文章说明查看 DBA_FREE_SPACE 慢的原因。 Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)  查询 DBA_FREE_SPACE 视图都有哪些表组成： set long 99999 line 29999 pages 49999 select text from dba_views where view_name='DBA_FREE_SPACE'; TEXT -------------------------------------------------------------------------------- select ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file# from sys.ts$ ts, sys.fet$ f, sys.file$ fi where ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0 union all select ts.name, fi.file#, f.ktfbfebno, f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi where ts.ts# = f.ktfbfetsn and f.ktfbfetsn = fi.ts# and f.ktfbfefno = fi.relfile# and ts.bitmapped 0 and ts.online$ in (1,4) and ts.contents$ = 0 and bitand(ts.flags, 4503599627370496) 4503599627370496 union all select ts.name, fi.file#, u.ktfbuebno, u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi where ts.ts# = rb.ts# and rb.ts# = fi.ts# and u.ktfbuefno = fi.relfile# and u.ktfbuesegtsn = rb.ts# and u.ktfbuesegfno = rb.file# and u.ktfbuesegbno = rb.block# and ts.bitmapped 0 and ts.online$ in (1,4) and ts.contents$ = 0 and bitand(ts.flags, 4503599627370496) 4503599627370496 union all select ts.name, fi.file#, u.block#, u.length * ts.blocksize, u.length, u.file# from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb where ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile# and u.ts# = rb.ts# and u.segfile# = rb.file# and u.segblock# = rb.block# and ts.bitmapped = 0 union all select ts.name, fi.file#, f.extent_start, (f.extent_length_blocks_2K /(ts.blocksize/2048)) * ts.blocksize, (f.extent_length_blocks_2K / (ts.blocksize/2048)), fi.relfile# from sys.ts$ ts, sys.new_lost_write_extents$ f, sys.file$ fi where ts.ts# = f.extent_datafile_tsid and f.extent_datafile_tsid = fi.ts# and ts.bitmapped 0 and ts.online$ in (1,4) and ts.contents$ = 0 and bitand(ts.flags, 4503599627370496) = 4503599627370496清理回收站查看回收站对象数及对象名 select count(1) from dba_recyclebin; COUNT(1) ---------- 144 Elapsed: 00:00:00.07 --查看回收站对象 select owner,object_name,original_name from dba_recyclebin;确认回收站对象可以清除后，我们来清理回收站，要注意的两点就是确认回收站的对象是否可以清理，以及各个 PDB 容器下的回收站都要清理，不单单是 CDB 容器。 purge dba_recyclebin; 然后查看 DBA_FREE_SPACE 和 表空间使用率都可以秒出结果。 另一套 11g 测试库查询表空间使用率需要 1 分 23 秒，收集统计信息也是无果。  那么我们来看一下回收站大小 80.375M，对象有 948个，看来回收站对象还是挺多的了。 SQL> select sum(SPACE)*8/1024 Total_MB from dba_recyclebin; TOTAL_MB ---------- 80.375 SQL> select count(*) from dba_recyclebin; COUNT(*) ---------- 948 SQL> purge dba_recyclebin;
DBA Recyclebin purged.
Elapsed: 00:00:48.84那么我们确认可清理后，清理回收站完成之后，查询表空间使用率结果也是秒出了。 写在最后 总的来说，查询表空间使用率如果比较慢对于 DBA 管理人员而言还是比较不耐烦的，当需要快速查看某个表空间时需要等待一两分钟不太友好，如果监控时也是使用的这个 SQL 查看使用率，试想每 5 分钟执行一次，那么看到的慢 SQL 有 99% 的都是查询表空间的，这不是打脸么，那么遇到这样的问题第一反应则是查看执行计划，收集统计信息，如果无果，那就查看回收站对象，如果回收站对象过多，则会导致查看 dba_free_space 变慢，我们需要确认对象是否可清理，清理完成后便可以秒出结果。 |
【本文地址】